﻿using BridgeWebSystemLib.Core.DataEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace BridgeWebSystemLib.Core.DataAccess
{
    public class SQLServerInstance : IDataAccess
    {
        private SqlConnection _conn;
        private string _connectionString;

        public SQLServerInstance(string connectionString)
        {
            _connectionString = connectionString;
        }

        public string ConnectionString
        {
            get { return _connectionString; }
        }

        private SqlConnection _dbConnection
        {
            get
            {
                if (_conn == null)
                {
                    _conn = new SqlConnection(_connectionString);
                    if (_conn.State != ConnectionState.Open)
                    {
                        _conn.Open();
                    }
                }
                return _conn;
            }
        }

        public IDbConnection GetConnection()
        {
            return _dbConnection;
        }

        public void CloseConnection()
        {
            _dbConnection.Close();
        }

        public dynamic GetScalar(string sql, object param = null,IDbTransaction transaction = null)
        {
            object objValue = null;
            if (param != null)
            {
                var sqlParamList = CreateSqlParameterArray(param);
                objValue = _dbConnection.ExecuteScalar(sql, transaction as SqlTransaction, sqlParamList);
            }
            else
                objValue = _dbConnection.ExecuteScalar(sql, transaction as SqlTransaction);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return objValue;
        }

        #region Get
        public T Get<T>(dynamic id, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var key = DataAccessCache.GetSingleKey<T>();
            if (key == null)
                throw new Exception(type.Name +"无主键字段！");
            var name = DataAccessCache.GetTableName(type);
            var properties = DataAccessCache.EntityFieldPropertiesCache(type);
            var sql = $"select {properties.Select(t => t.Name).Aggregate((a, b) => (a + "," + b))} from {name} where {key.Name} = @id";
            var param = new List<SQLParameter>();
            param.Add(new SQLParameter("id", id));
            return Get<T>(sql, param, transaction);
        }

        public T Get<T>(string sql, object param = null, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var properties = DataAccessCache.EntityFieldPropertiesCache(type);
            var sqlParamList = CreateSqlParameterArray(param);
            T t = default;
            using (var reader = _dbConnection.ExecuteReader(sql, transaction as SqlTransaction, sqlParamList))
            {
                t = SetEntityValueByDataReader<T>(reader, properties);
            }
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return t;
        }

        public T GetByParam<T>(object param, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var name = DataAccessCache.GetTableName(type);
            var properties = DataAccessCache.EntityFieldPropertiesCache(type);
            var sqlSb = new StringBuilder();
            sqlSb.Append($"select {properties.Select(t => t.Name).Aggregate((a, b) => (a + "," + b))} from {name} where (1=1)");
            if (param is List<SQLParameter>)
            {
                foreach (var p in param as List<SQLParameter>)
                {
                    if (IsValidValue(p.Value))
                    {
                        sqlSb.Append(" and " + p.ColumnName + " " + SQLParameter.ConvertOperator(p.Operator));
                        if (p.Operator == 运算符.模糊)
                        {
                            sqlSb.Append(" @%" + p.ParamName + "%");
                        }
                        else
                        {
                            sqlSb.Append(" @" + p.ParamName);
                        }
                    }
                }
            }
            else if (param is SQLParameter[])
            {
                foreach (var p in param as SQLParameter[])
                {
                    if (IsValidValue(p.Value))
                    {
                        sqlSb.Append(" and " + p.ColumnName + " " + SQLParameter.ConvertOperator(p.Operator));
                        if (p.Operator == 运算符.模糊)
                        {
                            sqlSb.Append(" @%" + p.ParamName + "%");
                        }
                        else
                        {
                            sqlSb.Append(" @" + p.ParamName);
                        }
                    }
                }
            }
            else
            {
                var paramProperties = param.GetType().GetProperties();
                for (var i = 0; i < paramProperties.Count(); i++)
                {
                    var property = paramProperties[i];
                    if (IsValidValue(property.GetValue(param)))
                    {
                        sqlSb.Append(" and " + property.Name + "=@" + property.Name);
                    }
                }
            }
            return Get<T>(sqlSb.ToString(), param, transaction);
        }
        #endregion

        #region Query
        public List<T> Query<T>(IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            
            var name = DataAccessCache.GetTableName(type);
            var properties = DataAccessCache.EntityFieldPropertiesCache(type);
            string sql = $"select {properties.Select(t => t.Name).Aggregate((a, b) => (a + "," + b))} from " + name;
            return Query<T>(sql, null, transaction);
        }

        public List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var properties = DataAccessCache.EntityFieldPropertiesCache(type);
            var sqlParamList = CreateSqlParameterArray(param);
            List<T> list = new List<T>();
            using (var reader = _dbConnection.ExecuteReader(sql, transaction as SqlTransaction, sqlParamList))
            {
                list = SetEntitiesValueByDataReader<T>(reader, properties);
            }
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return list;
        }

        public DataTable Query(string sql, object param = null, IDbTransaction transaction = null)
        {
            var oracleParamList = CreateSqlParameterArray(param);
            DataTable dataTable = _dbConnection.ExecuteDataTable(sql, oracleParamList);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return dataTable;
        }

        public List<T> QueryByParam<T>(object param, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var name = DataAccessCache.GetTableName(type);
            var properties = DataAccessCache.EntityFieldPropertiesCache(type);
            var sqlSb = new StringBuilder();
            sqlSb.Append($"select {properties.Select(t => t.Name).Aggregate((a, b) => (a + "," + b))} from {name} where (1=1)");
            if (param is List<SQLParameter>)
            {
                foreach (var p in param as List<SQLParameter>)
                {
                    if (IsValidValue(p.Value))
                    {
                        sqlSb.Append(" and " + p.ColumnName + " " + SQLParameter.ConvertOperator(p.Operator));
                        if (p.Operator == 运算符.模糊)
                        {
                            sqlSb.Append(" @%" + p.ParamName + "%");
                        }
                        else
                        {
                            sqlSb.Append(" @" + p.ParamName);
                        }
                    }
                }
            }
            else if (param is SQLParameter[])
            {
                foreach (var p in param as SQLParameter[])
                {
                    if (IsValidValue(p.Value))
                    {
                        sqlSb.Append(" and " + p.ColumnName + " " + SQLParameter.ConvertOperator(p.Operator));
                        if (p.Operator == 运算符.模糊)
                        {
                            sqlSb.Append(" @%" + p.ParamName + "%");
                        }
                        else
                        {
                            sqlSb.Append(" @" + p.ParamName);
                        }
                    }
                }
            }
            else
            {
                var paramProperties = param.GetType().GetProperties();
                for (var i = 0; i < paramProperties.Count(); i++)
                {
                    var property = paramProperties[i];
                    if (IsValidValue(property.GetValue(param)))
                    {
                        sqlSb.Append(" and " + property.Name + "=@" + property.Name);
                    }
                }
            }
            return Query<T>(sqlSb.ToString(), param, transaction);
        }
        #endregion

        Dictionary<string, int> sequencesDict = null;
        public int GetSequences<T>(IDbTransaction transaction = null) where T : class
        {
            string tableName = DataAccessCache.GetTableName(typeof(T));
            //if (sequencesDict == null)
            //    sequencesDict = new Dictionary<string, int>();
            //int seq = 0;
            //if (sequencesDict.ContainsKey(tableName))
            //{
            //    sequencesDict[tableName]++;
            //    seq = sequencesDict[tableName];
            //}
            //else
            //{
            //    string sSql = "SELECT IDENT_CURRENT('" + tableName + "') + (SELECT IDENT_INCR('" + tableName + "'))";
            //    var objValue = _dbConnection.ExecuteScalar(sSql, transaction as SqlTransaction);
            //    if (objValue == DBNull.Value)
            //    {
            //        throw new Exception("批量Insert需执行BatchSave方法！");
            //    }
            //    seq = (int)objValue;
            //    sequencesDict.Add(tableName, seq);
            //}
            //return seq;

            string sSql = string.Format("SELECT NEXT VALUE FOR {0}_SEQ", tableName);
            var objValue = _dbConnection.ExecuteScalar(sSql, transaction as SqlTransaction);
            if (objValue == DBNull.Value)
            {
                throw new Exception("表["+ tableName + "]没有序列！");
            }
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return (int)objValue;
        }

        public DateTime GetServerDateTime()
        {
            string sSql = "select GETDATE()";
            DateTime currentDateTime = (DateTime)_dbConnection.ExecuteScalar(sSql);
            _dbConnection.Close();
            return currentDateTime;
        }

        public int Insert<T>(T entity, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            if (type.IsArray)
            {
                throw new Exception("批量Insert需执行BatchSave方法！");
            }
            else if (type.IsGenericType)
            {
                var typeInfo = type.GetTypeInfo();
                bool implementsGenericIEnumerableOrIsGenericIEnumerable =
                    typeInfo.ImplementedInterfaces.Any(ti => ti.IsGenericType && ti.GetGenericTypeDefinition() == typeof(IEnumerable<>)) ||
                    typeInfo.GetGenericTypeDefinition() == typeof(IEnumerable<>);
                if (implementsGenericIEnumerableOrIsGenericIEnumerable)
                {
                    throw new Exception("批量Insert需执行BatchSave方法！");
                }
            }
            var name = DataAccessCache.GetTableName(type);
            var keyProperty = DataAccessCache.GetSingleKey<T>();
            var allProperties = DataAccessCache.EntityFieldPropertiesCache(type);
            var computedProperties = DataAccessCache.ComputedPropertiesCache(type);
            var allPropertiesExceptKeyAndComputed = allProperties.Except(computedProperties).ToList();
            var sbColumnList = new StringBuilder(null);
            var sbParameterList = new StringBuilder(null);
            for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
            {
                var property = allPropertiesExceptKeyAndComputed[i];
                //if (keyProperty != null && property.Name == keyProperty.Name)
                //    continue;
                sbColumnList.Append(property.Name);
                sbParameterList.AppendFormat("@{0}", property.Name);
                if (i < allPropertiesExceptKeyAndComputed.Count - 1)
                {
                    sbColumnList.Append(", ");
                    sbParameterList.Append(", ");
                }
            }
            var sql = $"insert into {name} ({sbColumnList}) values ({sbParameterList})";
            List<SqlParameter> parameters = new List<SqlParameter>();
            for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
            {
                var paramProperty = allPropertiesExceptKeyAndComputed[i];
                //if (keyProperty != null && paramProperty.Name == keyProperty.Name)
                //    continue;
                parameters.Add(new SqlParameter("@" + paramProperty.Name, paramProperty.GetValue(entity)));
            }
            int returnValue = _dbConnection.ExecuteNonQuery(sql, parameters.ToArray(), transaction as SqlTransaction);
            if (returnValue > 0)
                type.GetProperty("DataEntityState").SetValue(entity, DataEntityState.Modify);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return returnValue;
        }

        public int Update<T>(T entity, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var keyProperties = DataAccessCache.KeyPropertiesCache(type);
            if (keyProperties.Count == 0)
                throw new Exception("实体" + nameof(entity) + "不包含主键！");
            var name = DataAccessCache.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set ", name);
            var allProperties = DataAccessCache.EntityFieldPropertiesCache(type);
            var computedProperties = DataAccessCache.ComputedPropertiesCache(type);
            var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
            var parameters = new List<SqlParameter>();
            for (var i = 0; i < nonIdProps.Count; i++)
            {
                var property = nonIdProps[i];
                sb.Append(property.Name + " =@" + property.Name);
                parameters.Add(new SqlParameter("@" + property.Name, property.GetValue(entity)));
                if (i < nonIdProps.Count - 1)
                    sb.Append(", ");
            }
            sb.Append(" where ");
            for (var i = 0; i < keyProperties.Count; i++)
            {
                var property = keyProperties[i];
                sb.Append(property.Name + " =@" + property.Name);
                parameters.Add(new SqlParameter("@" + property.Name, property.GetValue(entity)));
                if (i < keyProperties.Count - 1)
                    sb.Append(" and ");
            }
            int returnValue = _dbConnection.ExecuteNonQuery(sb.ToString(), parameters.ToArray(), transaction as SqlTransaction);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return returnValue;
        }

        public int Execute(string sql, object param = null, IDbTransaction transaction = null)
        {
            var sqlParamList = CreateSqlParameterArray(param);
            int returnValue = _dbConnection.ExecuteNonQuery(sql, sqlParamList, transaction as SqlTransaction);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return returnValue;
        }

        #region Delete
        public int Delete<T>(T entity, IDbTransaction transaction = null) where T : class
        {
            if (entity == null)
                throw new Exception("实体" + nameof(entity) + "对象不能为空！");
            var type = typeof(T);
            var keyProperties = DataAccessCache.KeyPropertiesCache(type);
            if (keyProperties.Count == 0)
                throw new Exception("实体" + nameof(entity) + "不包含主键！");
            var name = DataAccessCache.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where ", name);
            var parameters = new List<SqlParameter>();
            for (var i = 0; i < keyProperties.Count; i++)
            {
                var property = keyProperties[i];
                sb.Append(property.Name + " =@" + property.Name);
                parameters.Add(new SqlParameter("@" + property.Name, property.GetValue(entity)));
                if (i < keyProperties.Count - 1)
                    sb.Append(" and ");
            }
            int returnValue = _dbConnection.ExecuteNonQuery(sb.ToString(), parameters.ToArray(), transaction as SqlTransaction);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return returnValue;
        }

        public int Delete<T>(dynamic id, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var key = DataAccessCache.GetSingleKey<T>();
            if (key == null)
                throw new Exception(type.Name + "无主键字段！");
            var name = DataAccessCache.GetTableName(type);
            var sql = $"delete from {name} where {key.Name} = @id";
            int returnValue = _dbConnection.ExecuteNonQuery(sql, new SqlParameter[] { new SqlParameter("@id", id) }, transaction as SqlTransaction);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return returnValue;
        }

        public int DeleteByParam<T>(object param, IDbTransaction transaction = null) where T : class
        {
            var type = typeof(T);
            var name = DataAccessCache.GetTableName(type);
            var sqlSb = new StringBuilder();
            sqlSb.Append($"delete from {name} where ");
            var paramProperties = param.GetType().GetProperties();
            for (var i = 0; i < paramProperties.Count(); i++)
            {
                var property = paramProperties[i];
                sqlSb.Append(property.Name + "=@" + property.Name);
                if (i < paramProperties.Count() - 1)
                {
                    sqlSb.Append(" and ");
                }
            }
            var sqlParamList = CreateSqlParameterArray(param);
            int returnValue = _dbConnection.ExecuteNonQuery(sqlSb.ToString(), sqlParamList, transaction as SqlTransaction);
            if (transaction == null)
            {
                _dbConnection.Close();
            }
            return returnValue;
        }
        #endregion

        private SqlParameter[] CreateSqlParameterArray(object param)
        {

            var sqlParamList = new List<SqlParameter>();
            if (param != null)
            {
                if (param is List<SQLParameter>)
                {
                    foreach (var p in param as List<SQLParameter>)
                    {
                        string paramName = p.ParamName;
                        if (!p.ParamName.StartsWith("@"))
                        {
                            p.ParamName = "@" + p.ParamName;
                        }
                        sqlParamList.Add(new SqlParameter(p.ParamName, p.Value));
                    }
                }
                else if (param is SQLParameter[])
                {
                    foreach (var p in param as SQLParameter[])
                    {
                        if (!p.ParamName.StartsWith("@"))
                        {
                            p.ParamName = "@" + p.ParamName;
                        }
                        sqlParamList.Add(new SqlParameter(p.ParamName, p.Value));
                    }
                }
                else
                {
                    var paramProperties = param.GetType().GetProperties();
                    foreach (var p in paramProperties)
                    {
                        object obj = p.GetValue(param);
                        string paramName = p.Name;
                        if (!p.Name.StartsWith("@"))
                        {
                            paramName = "@" + p.Name;
                        }
                        sqlParamList.Add(new SqlParameter(paramName, obj));
                    }
                }
            }
            return sqlParamList.ToArray();
        }

        private static bool IsValidValue(object value)
        {
            if (value == null || value is DBNull)
                return false;
            if (value is string)
            {
                if (string.IsNullOrWhiteSpace(value.ToString()) || string.IsNullOrEmpty(value.ToString()))
                    return false;
            }
            else if (value is decimal)
            {
                if ((decimal)value == decimal.MinValue)
                    return false;
            }
            else if (value is int)
            {
                if ((int)value == int.MinValue)
                    return false;
            }
            else if (value is DateTime)
            {
                if ((DateTime)value == DateTime.MinValue)
                    return false;
            }
            return true;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <param name="properties"></param>
        /// <returns></returns>
        private T SetEntityValueByDataReader<T>(IDataReader reader, List<PropertyInfo> properties)
        {
            T t = default;
            if (reader.Read())
            {
                t = (T)Activator.CreateInstance(typeof(T));
                for (var i = 0; i < reader.FieldCount; i++)
                {
                    if (!reader.IsDBNull(i))
                    {
                        var fieldName = reader.GetName(i);
                        var property = properties.FirstOrDefault(t => t.Name == fieldName);
                        if (property != null)
                        {
                            if (property.PropertyType == typeof(Int16) || property.PropertyType == typeof(Int16?))
                                property.SetValue(t, reader.GetInt16(i));
                            if (property.PropertyType == typeof(Int32) || property.PropertyType == typeof(Int32?))
                                property.SetValue(t, reader.GetInt32(i));
                            else if (property.PropertyType == typeof(decimal))
                                property.SetValue(t, reader.GetDecimal(i));
                            else if (property.PropertyType == typeof(string))
                                property.SetValue(t, reader.GetString(i));
                            else if (property.PropertyType == typeof(DateTime))
                                property.SetValue(t, reader.GetDateTime(i));
                            else if (property.PropertyType == typeof(byte[]))
                                property.SetValue(t, (byte[])reader.GetValue(i));
                        }
                    }
                }
                //for (var i = 0; i < properties.Count; i++)
                //{
                //    if (!reader.IsDBNull(i))
                //    {
                //        var fieldName = reader.GetName(i);
                //        var property = properties.FirstOrDefault(t => t.Name == fieldName);
                //        if (properties[i].PropertyType == typeof(Int16) || properties[i].PropertyType == typeof(Int16?))
                //            properties[i].SetValue(t, reader.GetInt16(i));
                //        if (properties[i].PropertyType == typeof(Int32) || properties[i].PropertyType == typeof(Int32?))
                //            properties[i].SetValue(t, reader.GetInt32(i));
                //        else if (properties[i].PropertyType == typeof(decimal))
                //            properties[i].SetValue(t, reader.GetDecimal(i));
                //        else if (properties[i].PropertyType == typeof(string))
                //            properties[i].SetValue(t, reader.GetString(i));
                //        else if (properties[i].PropertyType == typeof(DateTime))
                //            properties[i].SetValue(t, reader.GetDateTime(i));
                //        else if (properties[i].PropertyType == typeof(byte[]))
                //            properties[i].SetValue(t, (byte[])reader.GetValue(i));
                //    }
                //}
                var prop = typeof(T).GetProperty("DataEntityState");
                if (prop != null)
                    prop.SetValue(t, DataEntityState.Modify);
            }
            return t;
        }
        /// <summary>
        /// 使用DataReader设置实体的值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <param name="properties"></param>
        /// <returns></returns>
        private List<T> SetEntitiesValueByDataReader<T>(IDataReader reader, List<PropertyInfo> properties)
        {
            List<T> list = new List<T>();
            while (reader.Read())
            {
                T t = (T)Activator.CreateInstance(typeof(T));
                for (var i = 0; i < reader.FieldCount; i++)
                {
                    if (!reader.IsDBNull(i))
                    {
                        var fieldName = reader.GetName(i);
                        var property = properties.FirstOrDefault(t => t.Name == fieldName);
                        if (property != null)
                        {
                            if (property.PropertyType == typeof(Int16) || property.PropertyType == typeof(Int16?))
                                property.SetValue(t, reader.GetInt16(i));
                            else if (property.PropertyType == typeof(Int32) || property.PropertyType == typeof(Int32?))
                                property.SetValue(t, reader.GetInt32(i));
                            else if (property.PropertyType == typeof(decimal))
                                property.SetValue(t, reader.GetDecimal(i));
                            else if (property.PropertyType == typeof(string))
                                property.SetValue(t, reader.GetString(i));
                            else if (property.PropertyType == typeof(DateTime))
                                property.SetValue(t, reader.GetDateTime(i));
                            else if (property.PropertyType == typeof(byte[]))
                                property.SetValue(t, (byte[])reader.GetValue(i));
                        }
                    }
                }
                var prop = typeof(T).GetProperty("DataEntityState");
                if (prop != null)
                    prop.SetValue(t, DataEntityState.Modify);
                list.Add(t);
            }
            return list;
        }

        public void Dispose()
        {
            this.Dispose(true);////释放托管资源
            GC.SuppressFinalize(this);//请求系统不要调用指定对象的终结器. //该方法在对象头中设置一个位，系统在调用终结器时将检查这个位
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_isDisposed)//_isDisposed为false表示没有进行手动dispose
            {
                if (disposing)
                {
                    if (_conn != null)
                    {
                        _conn.Close();
                        _conn = null;
                    }
                }
                //清理非托管资源
            }
            _isDisposed = true;
            sequencesDict = null;
        }

        private bool _isDisposed;

        ~SQLServerInstance()
        {
            this.Dispose(false);//释放非托管资源，托管资源由终极器自己完成了
        }
    }
}
